package pe.adra.core.ext;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import pe.adra.core.dao.ADRAEntidad;
import pe.adra.core.dao.DAOUtil;
import pe.adra.core.ext.soporte.ColumnaBean;
import pe.adra.core.ext.soporte.SuportEntidad;
import pe.adra.core.ext.soporte.ListaBean;
import pe.adra.util.exception.DAOException;

@SuppressWarnings({ "rawtypes", "unchecked" })
public class QueryUtil {

	private static final Logger LOG = LoggerFactory.getLogger(QueryUtil.class);
	
	private QueryUtil() {
	}

	public static <ED extends ADRAEntidad> int count(DAOBaseImpl daobase, ED filtro, String consulta, Query qy) throws DAOException {
		return count(daobase, filtro, consulta, qy, null, null);
	}

	public static <ED extends ADRAEntidad> int count(DAOBaseImpl daobase, ED filtro, String consulta, Query qy, Date finit, Date ffin) throws DAOException {
		StringBuffer strbfSqlQuery = new StringBuffer();
		strbfSqlQuery.append(qy);
		daobase.procesarConsulta(filtro, finit, ffin, strbfSqlQuery);
		int count = (Integer) qy.getSingleResult();
		return count;
	}

	public static <ED extends ADRAEntidad> List<ED> getLista(DAOBaseImpl daobase, ED filtro, String consulta, Query qy, int inicio, int fin, String strSort, String strOrder, SuportEntidad... innerjoin) throws DAOException {
		return getLista(daobase, filtro, null, null, consulta, qy, inicio, fin, strSort, strOrder, innerjoin);
	}

	public static <ED extends ADRAEntidad> List<ED> getLista(DAOBaseImpl daobase, ED filtro, Date finit, Date ffin, String consulta, Query qy, int inicio, int fin, String strSort, String strOrder, SuportEntidad... innerjoin) throws DAOException {
		StringBuffer strbfSqlQuery = new StringBuffer();
		strbfSqlQuery.append(consulta);
		daobase.procesarConsulta(filtro, finit, ffin, strbfSqlQuery, innerjoin);
		DAOUtil.setPaginador(qy, inicio, fin);
		DAOUtil.setOrder(strbfSqlQuery, strSort, strOrder);
		List<ED> p = qy.getResultList();
		return p;
	}

	public static <ED extends ADRAEntidad> List<ED> getCbo(DAOBaseImpl daobase, ED filtro, String consulta, Query qy, String campoValue) throws DAOException {
		StringBuffer strbfSqlQuery = new StringBuffer();
		strbfSqlQuery.append(consulta);
		daobase.procesarConsulta(filtro, strbfSqlQuery);
		DAOUtil.setOrder(strbfSqlQuery, campoValue, DAOUtil.SQL_ASC);
		List<ED> p = qy.getResultList();
		return p;
	}

	public static <ED extends ADRAEntidad> List<ED> getPorID(DAOBaseImpl daobase, ED filtro, String queryLimite, String queryALL, Query qy, String campoValue, Integer[] ids, String tipo, SuportEntidad innerjoin) throws DAOException {
		StringBuffer strbfSqlQuery = new StringBuffer();
		if (tipo.equals(DAOUtil.LISTA_LIMItADA_CBO)) {
			strbfSqlQuery.append(queryLimite);
		} else if (tipo.equals(DAOUtil.LISTA_COMPLETA)) {
			strbfSqlQuery.append(queryALL);
		}
		daobase.procesarConsulta(filtro, ids, strbfSqlQuery, innerjoin);
		DAOUtil.setOrder(strbfSqlQuery, campoValue, DAOUtil.SQL_ASC);
		List<ED> p = qy.getResultList();
		return p;
	}

	private static String createQuery(boolean iscount, ListaBean lista, boolean _search, String searchField, String searchOper, String searcchString, String sidx, String sord, SuportEntidad... objConsultas) {
		StringBuilder builder = new StringBuilder();
		builder.append(" Select ");
		if (iscount) {
			builder.append(" count(p.id) ");
		} else {
			List<ColumnaBean> lst = new ArrayList<ColumnaBean>();
			for (int i = 0; i < lista.getColumnaBeans().size(); i++) {
				ColumnaBean c = lista.getColumnaBeans().get(i);
				if (c.getMapping()) {
					lst.add(c);
				}
			}
			for (int i = 0; i < lst.size(); i++) {
				ColumnaBean c = lst.get(i);
				builder.append(" p." + c.getAtributo() + " ");
				if (c.getMapping()) {
					if (i < lst.size() - 1) {
						builder.append(" , ");
					}
				}
			}
		}
		builder.append(" from " + lista.getTable() + " p ");
		builder.append(" where p.id>0 ");
		if (objConsultas != null && objConsultas.length > 0) {
			builder.append("  and ");
			for (int i = 0; i < objConsultas.length; i++) {
				SuportEntidad ojb = objConsultas[i];
				if (ojb.getIgual()) {
					Double d = Double.parseDouble(String.valueOf(ojb.getValor()));
					if (d > 0) {
						builder.append(" p." + ojb.getAtributo() + " = :" + ojb.getQuery());
						if (i < objConsultas.length - 1) {
							builder.append(" and ");
						}
					}
				}
				if (ojb.getLike()) {
					String str = String.valueOf(ojb.getValor());
					if (!str.equals("")) {
						builder.append(" p." + ojb.getAtributo() + " like :" + ojb.getQuery());
						if (i < objConsultas.length - 1) {
							builder.append("  and ");
						}
					}
				}
				if (ojb.getMenos()) {
					Double d = Double.parseDouble(String.valueOf(ojb.getValor()));
					if (d > 0) {
						builder.append(" p." + ojb.getAtributo() + " > :" + ojb.getQuery());
						if (i < objConsultas.length - 1) {
							builder.append(" and ");
						}
					}

				}
				if (ojb.getMayor()) {
					Double d = Double.parseDouble(String.valueOf(ojb.getValor()));
					if (d > 0) {
						builder.append(" p." + ojb.getAtributo() + " < :" + ojb.getQuery());
						if (i < objConsultas.length - 1) {
							builder.append(" and ");
						}
					}
				}

				if (ojb.getBetween()) {
					Double d = Double.parseDouble(String.valueOf(ojb.getValor()));
					if (d > 0) {
						builder.append(" p." + ojb.getAtributo() + "  between :" + ojb.getQuery() + " and :" + ojb.getQuery2());
						if (i < objConsultas.length - 1) {
							builder.append(" and ");
						}
					}
				}

			}
		}
		LOG.debug("createQuery:");
		LOG.debug(builder.toString());
		return builder.toString();
	}

	public static int getDataRows(EntityManager em, ListaBean lista, String sidx, String sord, int page, int rows, boolean _search, String searchField, String searchOper, String searchString, SuportEntidad... objConsultas) {
		String sqlQuery = createQuery(false, lista, _search, searchField, searchOper, sidx, sidx, sord, objConsultas);
		Query q = em.createQuery(sqlQuery);
		Long c = (Long) q.getSingleResult();
		return c.intValue();
	}

	public static List<Map<String, Object>> getData(EntityManager em, ListaBean lista, String sidx, String sord, int page, int rows, boolean _search, String searchField, String searchOper, String searchString, SuportEntidad... objConsultas) {
		String sqlQuery = createQuery(false, lista, _search, searchField, searchOper, sidx, sidx, sord, objConsultas);
		Query q = em.createQuery(sqlQuery);
		q.setFirstResult(rows * page - rows);
		q.setMaxResults(rows);

		if (objConsultas != null && objConsultas.length > 0) {
			for (int i = 0; i < objConsultas.length; i++) {
				SuportEntidad ojb = objConsultas[i];

				if (ojb.getIgual()) {
					Double d = Double.parseDouble(String.valueOf(ojb.getValor()));
					if (d > 0) {
						q.setParameter(ojb.getQuery(), ojb.getValor());
					}
				}

				if (ojb.getLike()) {
					String str = String.valueOf(ojb.getValor());
					if (!str.equals("")) {
						q.setParameter(ojb.getQuery(), "%" + ojb.getValor() + "%");
					}
				}

				if (ojb.getMenos()) {
					Double d = Double.parseDouble(String.valueOf(ojb.getValor()));
					if (d > 0) {
						q.setParameter(ojb.getQuery(), ojb.getValor());
					}
				}
				if (ojb.getMayor()) {
					Double d = Double.parseDouble(String.valueOf(ojb.getValor()));
					if (d > 0) {
						q.setParameter(ojb.getQuery(), ojb.getValor());
					}
				}

				if (ojb.getBetween()) {
					Double d = Double.parseDouble(String.valueOf(ojb.getValor()));
					if (d > 0) {
						q.setParameter(ojb.getQuery(), ojb.getValor());
						q.setParameter(ojb.getQuery2(), ojb.getValor2());
					}
				}
			}
		}

		List<Object[]> retorno = q.getResultList();

		if (retorno.size() > 0) {
			List<Map<String, Object>> salida = new ArrayList<Map<String, Object>>();
			for (Object[] dato : retorno) {
				Map<String, Object> data = new HashMap<String, Object>();
				for (int i = 0; i < dato.length; i++) {
					data.put(lista.getColumnaBeans().get(i).getCodigo(), dato[i]);
				}
				for (int i = 0; i < lista.getColumnaBeans().size(); i++) {
					ColumnaBean c = lista.getColumnaBeans().get(i);
					if (!c.getMapping()) {
						data.put(c.getCodigo(), "");
					}
				}
				salida.add(data);
			}
			return salida;
		}
		return null;
	}

}
